package com.pactera.service;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.pactera.db.PostgresDb;
import com.pactera.pojo.CmrAjf;
import com.pactera.pojo.SmsPhone;
import com.pactera.utils.DateUtil;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.tools.ant.util.StringUtils;

public class PostgresDBService {

	//获取所有失败作业
	public List<CmrAjf> getErrorTask(){
		// state取值为8的时候，作业完成;再进一步判断state为Y为OK，N为NOT OK
					String taskSql = "select * from cmr_ajf where status='N' and state='8' "
							+ "and odate='"
							+ DateUtil.DateToStr(new Date(),
									"yyyyMMdd") + "' order by applgroup,jobname";
//					System.out.println(taskSql);
					return (List<CmrAjf>)PostgresDb.query(taskSql, new BeanListHandler(
							CmrAjf.class));		
	}
	
	//查询某个作业是否成功
	public boolean isOk(String smsPhone){
		boolean result = false;
		String taskSql = "select * from cmr_ajf where status='Y' and state='8' "
				+ "and applgroup='"+smsPhone.split("\\.")[0]+"'"
				+ "and jobname='"+smsPhone.split("\\.")[1]	+"'"
				+ "and odate='"
				+ DateUtil.DateToStr(new Date(),"yyyyMMdd")+"'";
		List<CmrAjf> list = (List<CmrAjf>)PostgresDb.query(taskSql, new BeanListHandler(
				CmrAjf.class));	
		if(list!=null&&list.size()!=0){
			result = true;
		}
		return result;
	}


	//查询所有作业是否成功
	public boolean isAllOk(){
		boolean result = false;
		//成功的作业数
		String okTaskNumSql = "select count(*) from cmr_ajf where status='Y' and state='8' "
				+ "and orderno IN(select MAX(orderno) from public.cmr_ajf GROUP BY TRIM(APPLGROUP)||'.'||TRIM(jobname)) "
				+ "and odate='"
				+ DateUtil.DateToStr(new Date(),"yyyyMMdd")+"'";
		int oktasknum = new BigDecimal(String.valueOf(PostgresDb.query( okTaskNumSql,new ScalarHandler()))).intValue();

		//总作业数
		String alltaskNumSql = "select count(*) from cmr_ajf where odate='"
				+ DateUtil.DateToStr(new Date(),"yyyyMMdd")+"' "
				+ "and orderno IN(select MAX(orderno) from public.cmr_ajf GROUP BY TRIM(APPLGROUP)||'.'||TRIM(jobname)) ";

		int alltasknum = new BigDecimal(String.valueOf(PostgresDb.query( alltaskNumSql,new ScalarHandler()))).intValue();

		//如果所有作业数和完成作业数相等 且 大于0
		if(alltasknum==oktasknum&&alltasknum>0){
			result = true;
		}
		return result;
	}

	//查询某组作业是否成功
	public boolean groupIsOk(String smsPhone){
		String folderStr ="";
		if(smsPhone!=null||smsPhone!=""){
			folderStr = "'"+smsPhone.replaceAll(",", "','")+"'";
		}
		boolean result = false;
		//成功作业list
		String taskOkSql = "select count(*) from public.cmr_ajf where status='Y' and state='8' "
			+ "and orderno IN(select MAX(orderno) from public.cmr_ajf GROUP BY TRIM(APPLGROUP)||'.'||TRIM(jobname)) "
			+ "and applgroup in ("+folderStr+")"
			+ "and odate='"
			+ DateUtil.DateToStr(new Date(),"yyyyMMdd")+"'";
		int okjobnum = new BigDecimal(String.valueOf(PostgresDb.query( taskOkSql,new ScalarHandler()))).intValue();

		String allTaskSql = "select count(*) from public.cmr_ajf where 1=1 "
			+ "and orderno IN(select MAX(orderno) from public.cmr_ajf GROUP BY TRIM(APPLGROUP)||'.'||TRIM(jobname)) "
			+ "and applgroup in ("+folderStr+")"
			+ "and odate='"
			+ DateUtil.DateToStr(new Date(),"yyyyMMdd")+"'";
		int alljobnum = new BigDecimal(String.valueOf(PostgresDb.query( allTaskSql,new ScalarHandler()))).intValue();
		//如果所有作业数和完成作业数相等 且 大于0
		if(alljobnum==okjobnum&&alljobnum>0){
			result = true;
		}
		System.out.println("当天总作业数" + alljobnum + ", 完成作业数:" + okjobnum);
		return result;
	}

	
}
